package com.simpleauth.base;

import java.util.Map;

import org.apache.log4j.Logger;


public class SqlStatements {
	private static final Logger log = Logger.getLogger(SqlStatements.class);

	public SqlStatements() {};
	
	public String getAuthenticated (String username, String password) {
		String sql = "SELECT id FROM users WHERE name = '" + username + "' AND pass = '" + password + "'";
		return sql;
	}

	public String getServiceRole (String servicename, String rolename) {
		String sql = "select service_id, role_id from roles_services " +
                     "where service_id in " + 
		             "    (select svc.id as service_id from services svc " +
		             "     where svc.name = '" + servicename + "') " + 
		             "and role_id in " +  
		             "    (select r.id as role_id from roles r " + 
		             "     where r.name = '" + rolename + "') ";
		return sql;
	}
	
	public String getUserRole (String username, String rolename) {
		String sql = "select user_id, role_id from roles_users " +
                     "where user_id in " + 
                     "    (select usr.id as user_id from users usr " +
                     "     where usr.name = '" + username + "') " + 
                     "and role_id in " +  
                     "    (select r.id as role_id from roles r " + 
                     "     where r.name = '"+ rolename + "') ";
		return sql;
	}
	
	public String getRolesForUser (String servicename, String username) {
		String sql = " select r.name from roles r where r.id in ( " +
			         " select rs.role_id from roles_services rs " + 
		             " where rs.service_id in " + 
		             "   (select svc.id from services svc " +
		             "    where svc.name = '" + servicename + "') " +
		             " and rs.role_id in " + 
		             "   (select ru.role_id from roles_users ru " + 
		             "    where ru.user_id in " +
		             "      (select u.id from users u " +
		             "       where u.name = '" + username + "')" +
		             "   )" +
		             " )";
		return sql;
	}

}// end class
